import tkinter as tk
from tkinter import ttk

import pymysql


def connect_to_database():
    host = host_entry.get()
    user = user_entry.get()
    password = password_entry.get()
    db = db_entry.get()

    try:
        connection = pymysql.connect(host=host, user=user, password=password, db=db)
        cursor = connection.cursor()
        cursor.execute("SELECT DATABASE()")
        result = cursor.fetchone()
        result_text.insert(tk.END, f"Connected to database: {result[0]}\n")
        root.withdraw()  # 隐藏当前窗口
        open_new_window()  # 连接成功后打开新窗口
    except Exception as e:
        result_text.insert(tk.END, f"Failed to connect to database: {e}\n")


def open_new_window():
    new_window = tk.Tk()
    new_window.geometry("800x600")

    def open_window1():
        new_window.withdraw()
        window1 = tk.Toplevel()
        window1.geometry("800x600")
        frame = tk.Frame(window1)
        frame.pack()

        # 连接到数据库
        connection = pymysql.connect(host='localhost', user='root', password='wjj20040123', db='librarydb')
        cursor = connection.cursor()
        books_text = tk.Text(window1, width=50, height=10)
        books_text.pack()
        transactions_text = tk.Text(window1, width=50, height=10)
        transactions_text.pack()

        update_table(books_text, cursor, "books")
        update_table(transactions_text, cursor, "transactions")

        # 查询books表中的所有bookID
        cursor.execute("SELECT bookID FROM books")
        book_ids = cursor.fetchall()
        # 创建一个标签，显示在下拉框的左边
        var_label = tk.Label(frame, text="选择要删除的bookID：")
        var_label.pack(side=tk.LEFT)
        # 创建一个下拉框，用于选择bookID
        book_id_var = tk.StringVar()
        combobox = ttk.Combobox(frame, textvariable=book_id_var)
        combobox['values'] = [str(book_id[0]) for book_id in book_ids]
        combobox.pack(side=tk.LEFT)
        # 创建一个标签，显示在文本框的左边
        sql_label = tk.Label(window1, text="SQL指令：")
        sql_label.pack(side=tk.LEFT)
        # 创建一个文本框用于显示SQL指令
        sql_text = tk.Text(window1, width=50, height=10)
        sql_text.pack(side=tk.LEFT)

        # 创建一个确认按钮，当点击这个按钮时，获取所有被选中的bookID，然后构造一个SQL指令，并将这个指令发送到数据库
        def send_sql():
            selected_book_ids = book_id_var.get()
            try:
                with connection.cursor() as cursor:
                    # 开始事务
                    start_transaction_sql = "START TRANSACTION;"
                    cursor.execute(start_transaction_sql)
                    sql_text.insert(tk.END, start_transaction_sql + "\n")

                    # 删除与该图书相关的交易记录
                    delete_transactions_sql = "DELETE FROM Transactions WHERE BookID = %s;" % selected_book_ids
                    cursor.execute(delete_transactions_sql)
                    sql_text.insert(tk.END, delete_transactions_sql + "\n")

                    # 删除图书记录
                    delete_books_sql = "DELETE FROM Books WHERE BookID = %s;" % selected_book_ids
                    cursor.execute(delete_books_sql)
                    sql_text.insert(tk.END, delete_books_sql + "\n")

                    # 提交事务
                    commit_sql = "COMMIT;"
                    cursor.execute(commit_sql)
                    sql_text.insert(tk.END, commit_sql + "\n")
                    update_table(books_text, cursor, "books")
                    update_table(transactions_text, cursor, "transactions")


            except Exception as e:
                # 发生异常时回滚事务
                connection.rollback()
                print(f"An error occurred: {e}")
            finally:
                connection.close()  # 确保无论如何都关闭连接

        confirm_button = tk.Button(window1, text="确认", command=send_sql)
        confirm_button.pack()

        tk.Button(window1, text="返回", command=lambda: [window1.destroy(), new_window.deiconify()]).pack()
        window1.protocol("WM_DELETE_WINDOW", lambda: [window1.destroy(), new_window.deiconify()])  # 当窗口关闭时，显示上一级窗口

        window1.mainloop()

    def open_window2():
        new_window.withdraw()
        window2 = tk.Toplevel()
        window2.geometry("800x600")
        frame = tk.Frame(window2)
        frame.pack()

        # 连接到数据库
        connection = pymysql.connect(host='localhost', user='root', password='wjj20040123', db='librarydb')
        cursor = connection.cursor()
        input_frame = tk.Frame(window2)
        input_frame.pack()
        # 创建一个标签和一个输入框，用于输入book_id
        book_id_label = tk.Label(input_frame, text="Book ID:")
        book_id_label.pack(side=tk.LEFT)
        book_id_entry = tk.Entry(input_frame)
        #book_id_entry.pack(side=tk.LEFT)

        # 创建一个标签和一个输入框，用于输入book_title
        book_title_label = tk.Label(input_frame, text="Book Title:")
        book_title_label.pack(side=tk.LEFT)
        book_title_entry = tk.Entry(input_frame)
        book_title_entry.pack(side=tk.LEFT)

        # 创建一个标签和一个输入框，用于输入author_id
        author_id_label = tk.Label(input_frame, text="Author ID:")
        author_id_label.pack(side=tk.LEFT)
        author_id_entry = tk.Entry(input_frame)
        author_id_entry.pack(side=tk.LEFT)

        # 创建一个标签和一个输入框，用于输入published_year
        published_year_label = tk.Label(input_frame, text="Published Year:")
        published_year_label.pack(side=tk.LEFT)
        published_year_entry = tk.Entry(input_frame)
        published_year_entry.pack(side=tk.LEFT)
        books_text = tk.Text(window2, width=50, height=10)
        books_text.pack()
        transactions_text = tk.Text(window2, width=50, height=10)
        transactions_text.pack()

        update_table(books_text, cursor, "books")
        update_table(transactions_text, cursor, "authors")

        # 在window2中创建一个新的Frame，用于放置输入框和它们的标签
        input_frame = tk.Frame(window2)
        input_frame.pack()

        # 创建一个标签，显示在文本框的左边
        sql_label = tk.Label(window2, text="SQL指令：")
        sql_label.pack(side=tk.LEFT)
        # 创建一个文本框用于显示SQL指令
        sql_text = tk.Text(window2, width=50, height=10)
        sql_text.pack(side=tk.LEFT)

        # 创建一个确认按钮，当点击这个按钮时，获取所有被选中的bookID，然后构造一个SQL指令，并将这个指令发送到数据库
        def send_sql():
            # 定义删除触发器的SQL语句
            drop_trigger_sql = "DROP TRIGGER IF EXISTS add_transaction;"
            sql_text.insert(tk.END, drop_trigger_sql + "\n")  # 插入到文本框

            # 定义创建触发器的SQL语句
            create_trigger_sql = """
            CREATE TRIGGER add_transaction
            AFTER INSERT ON Books
            FOR EACH ROW
            BEGIN
                UPDATE Authors SET BookCount = BookCount + 1 WHERE AuthorID = NEW.AuthorID;
            END;
            """
            sql_text.insert(tk.END, create_trigger_sql + "\n")  # 插入到文本框

            # 从输入框获取数据
            book_id = book_id_entry.get()
            book_title = book_title_entry.get()
            author_id = author_id_entry.get()
            published_year = published_year_entry.get()

            try:
                with connection.cursor() as cursor:
                    # 执行删除触发器的SQL语句
                    cursor.execute(drop_trigger_sql)

                    # 执行创建触发器的SQL语句
                    cursor.execute(create_trigger_sql)

                    # 添加新的书籍记录
                    add_book_sql = "INSERT INTO Books (Title, AuthorID, PublishedYear) VALUES ('%s', %s, %s);" % (
                        book_title, author_id, published_year)
                    cursor.execute(add_book_sql)
                    sql_text.insert(tk.END, add_book_sql + "\n")  # 插入到文本框

                    # 提交事务
                    commit_sql = "COMMIT;"
                    cursor.execute(commit_sql)
                    sql_text.insert(tk.END, commit_sql + "\n")  # 插入到文本框
                    update_table(books_text, cursor, "books")
                    update_table(transactions_text, cursor, "authors")
            except Exception as e:
                # 发生异常时回滚事务
                connection.rollback()
                print(f"An error occurred: {e}")
                sql_text.insert(tk.END, f"An error occurred: {e}" + "\n")
            #finally:
                 #connection.close()  # 确保无论如何都关闭连接

        confirm_button = tk.Button(window2, text="确认", command=send_sql)
        confirm_button.pack()
        tk.Button(window2, text="返回", command=lambda: [window2.destroy(), new_window.deiconify()]).pack()
        window2.protocol("WM_DELETE_WINDOW", lambda: [window2.destroy(), new_window.deiconify()])  # 当窗口关闭时，显示上一级窗口
        connection.close()
        window2.mainloop()

    def open_window3():
        new_window.withdraw()
        window3 = tk.Toplevel()
        window3.geometry("800x600")
        frame = tk.Frame(window3)
        frame.pack()

        # 连接到数据库
        connection = pymysql.connect(host='localhost', user='root', password='wjj20040123', db='librarydb')
        cursor = connection.cursor()

        # 查询books表中的所有bookID
        cursor.execute("SELECT BookID FROM Books")
        book_ids = cursor.fetchall()

        # 创建一个标签，显示在下拉框的左边
        book_id_label = tk.Label(frame, text="选择要更改的书籍ID：")
        book_id_label.pack(side=tk.LEFT)

        # 创建一个下拉框，用于选择bookID
        book_id_var = tk.StringVar()
        combobox = ttk.Combobox(frame, textvariable=book_id_var)
        combobox['values'] = [str(book_id[0]) for book_id in book_ids]
        combobox.pack(side=tk.LEFT)

        # 创建一个新的Frame，用于放置输入框和它们的标签
        input_frame = tk.Frame(window3)
        input_frame.pack()

        # 创建一个标签和一个输入框，用于输入book_id
        book_id_label = tk.Label(input_frame, text="Book ID:")
        book_id_label.pack(side=tk.LEFT)
        book_id_entry = tk.Entry(input_frame)
        book_id_entry.pack(side=tk.LEFT)

        # 创建一个新的Frame，用于放置输入框和它们的标签
        input_frame2 = tk.Frame(window3)
        input_frame2.pack()

        # 创建一个标签和一个输入框，用于输入book_title
        book_title_label = tk.Label(input_frame2, text="Book Title:")
        book_title_label.pack(side=tk.LEFT)
        book_title_entry = tk.Entry(input_frame2)
        book_title_entry.pack(side=tk.LEFT)

        # 创建一个新的Frame，用于放置输入框和它们的标签
        input_frame3 = tk.Frame(window3)
        input_frame3.pack()

        # 创建一个标签和一个输入框，用于输入author_id
        author_id_label = tk.Label(input_frame3, text="Author ID:")
        author_id_label.pack(side=tk.LEFT)
        author_id_entry = tk.Entry(input_frame3)
        author_id_entry.pack(side=tk.LEFT)

        # 创建一个新的Frame，用于放置输入框和它们的标签
        input_frame4 = tk.Frame(window3)
        input_frame4.pack()

        # 创建一个标签和一个输入框，用于输入published_year
        published_year_label = tk.Label(input_frame4, text="Published Year:")
        published_year_label.pack(side=tk.LEFT)
        published_year_entry = tk.Entry(input_frame4)
        published_year_entry.pack(side=tk.LEFT)

        # 创建一个文本框用于显示books表的内容
        books_text = tk.Text(window3, width=50, height=10)
        books_text.pack()


        update_table(books_text, cursor, "books")

        # 创建一个文本框用于显示SQL指令
        sql_text = tk.Text(window3, width=50, height=10)
        sql_text.pack(side=tk.LEFT)

        # 当下拉框的值改变时，更新输入框的值
        def on_combobox_changed(event):
            book_id = book_id_var.get()
            cursor.execute(f"SELECT Title, AuthorID, PublishedYear FROM Books WHERE BookID = {book_id}")
            book = cursor.fetchone()
            book_id_entry.delete(0, tk.END)
            book_id_entry.insert(0, book_id)
            book_title_entry.delete(0, tk.END)
            book_title_entry.insert(0, book[0])
            author_id_entry.delete(0, tk.END)
            author_id_entry.insert(0, book[1])
            published_year_entry.delete(0, tk.END)
            published_year_entry.insert(0, book[2])

        combobox.bind("<<ComboboxSelected>>", on_combobox_changed)

        # 创建一个确认按钮，当点击这个按钮时，获取所有被选中的bookID，然后构造一个SQL指令，并将这个指令发送到数据库
        def send_sql():
            # 定义删除存储过程的SQL语句
            drop_procedure_sql = "DROP PROCEDURE IF EXISTS UpdateBook;"

            # 定义创建存储过程的SQL语句
            create_procedure_sql = """
            CREATE PROCEDURE UpdateBook(IN book_id INT, IN book_title VARCHAR(255), IN author_id INT, IN published_year INT)
            BEGIN
                    DECLARE author_exists INT;
                    SELECT COUNT(*) INTO author_exists
                    FROM Authors
                    WHERE AuthorID = author_id;
                
                    IF author_exists > 0 THEN
                        UPDATE Books 
                        SET Title = book_title, AuthorID = author_id, PublishedYear = published_year 
                        WHERE BookID = book_id;
                    ELSE
                        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'AuthorID does not exist.';
                    END IF;
            END;
            """
            # 从输入框获取数据
            book_id = book_id_entry.get()
            book_title = book_title_entry.get()
            author_id = author_id_entry.get()
            published_year = published_year_entry.get()

            try:
                with connection.cursor() as cursor:
                    cursor.execute(drop_procedure_sql)
                    sql_text.insert(tk.END, drop_procedure_sql + "\n")  # 插入到文本框

                    # 执行创建存储过程的SQL语句
                    cursor.execute(create_procedure_sql)
                    sql_text.insert(tk.END, create_procedure_sql + "\n")  # 插入到文本框

                    # 调用存储过程
                    call_procedure_sql = f"CALL UpdateBook({book_id}, '{book_title}', {author_id}, {published_year});"
                    cursor.execute(call_procedure_sql)
                    sql_text.insert(tk.END, call_procedure_sql + "\n")  # 插入到文本框

                    # 提交事务
                    connection.commit()
                    sql_text.insert(tk.END, "COMMIT;\n")  # 插入到文本框

                    # 更新books和authors表的内容
                    update_table(books_text, cursor, "books")

            except Exception as e:
                # 发生异常时回滚事务
                connection.rollback()
                print(f"An error occurred: {e}")
                sql_text.insert(tk.END, f"An error occurred: {e}" + "\n")



        confirm_button = tk.Button(window3, text="确认", command=send_sql)
        confirm_button.pack()
        tk.Button(window3, text="返回", command=lambda: [window3.destroy(), new_window.deiconify()]).pack()
        window3.protocol("WM_DELETE_WINDOW", lambda: [window3.destroy(), new_window.deiconify()])  # 当窗口关闭时，显示上一级窗口
        #connection.close()  # 确保无论如何都关闭连接
        window3.mainloop()

    def open_window4():
        new_window.withdraw()
        window4 = tk.Toplevel()
        window4.geometry("800x600")
        frame = tk.Frame(window4)
        frame.pack()

        # 创建一个文本框用于显示查询结果
        query_text = tk.Text(window4, width=50, height=10)
        query_text.pack()

        # 创建一个文本框用于显示SQL指令
        sql_text = tk.Text(window4, width=50, height=10)
        sql_text.pack(side=tk.LEFT)

        # 创建一个字典，键是显示的文本，值是对应的值
        attributes = {"Books.Title": "标题", "Authors.Name AS AuthorName": "作者姓名", "Books.PublishedYear": "出版日期"}
        attribute_vars = {attribute: tk.IntVar() for attribute in attributes.keys()}

        for attribute, var in attribute_vars.items():
            # 创建复选框，text参数设置显示的文本，onvalue和offvalue参数设置复选框被选中和未被选中时的值
            tk.Checkbutton(window4, text=attributes[attribute], variable=var, onvalue=1, offvalue=0).pack()
        # 创建一个确认按钮，当点击这个按钮时，执行查询操作
        def execute_query():


            # 连接到数据库
            connection = pymysql.connect(host='localhost', user='root', password='wjj20040123', db='librarydb')
            cursor = connection.cursor()

            # 获取用户选择的属性
            selected_attributes = [attribute for attribute, var in attribute_vars.items() if var.get() == 1]
            if not selected_attributes:
                return

            # 定义删除视图的SQL语句
            drop_view_sql = "DROP VIEW IF EXISTS BooksView;"

            # 定义创建视图的SQL语句
            create_view_sql = f"""
            CREATE OR REPLACE VIEW BooksView AS
             SELECT 
             {', '.join(selected_attributes)}
               
            FROM 
                Books
            JOIN 
                Authors ON Books.AuthorID = Authors.AuthorID;
            """

            # 定义查询视图的SQL语句
            query_view_sql = "SELECT * FROM BooksView;"
            print(query_view_sql)
            try:
                with connection.cursor() as cursor:
                    # 执行删除视图的SQL语句
                    cursor.execute(drop_view_sql)
                    sql_text.insert(tk.END, drop_view_sql + "\n")  # 插入到文本框
                    print(drop_view_sql)
                    # 执行创建视图的SQL语句
                    cursor.execute(create_view_sql)
                    sql_text.insert(tk.END, create_view_sql + "\n")  # 插入到文本框
                    print(create_view_sql)
                    # 执行查询视图的SQL语句
                    cursor.execute(query_view_sql)
                    sql_text.insert(tk.END, query_view_sql + "\n")  # 插入到文本框
                    print(query_view_sql)
                    # 获取查询结果
                    rows = cursor.fetchall()
                    # 清除文本框内容
                    query_text.delete("1.0", tk.END)
                    # 在文本框中显示查询结果
                    for row in rows:
                        query_text.insert(tk.END, f"{row}\n")
            except Exception as e:
                print(f"An error occurred: {e}")
                sql_text.insert(tk.END, f"An error occurred: {e}" + "\n")
            finally:
                connection.close()  # 确保无论如何都关闭连接

        confirm_button = tk.Button(window4, text="确认", command=execute_query)
        confirm_button.pack()
        tk.Button(window4, text="返回", command=lambda: [window4.destroy(), new_window.deiconify()]).pack()
        window4.protocol("WM_DELETE_WINDOW", lambda: [window4.destroy(), new_window.deiconify()])  # 当窗口关闭时，显示上一级窗口
        window4.mainloop()
    def go_back():
        new_window.destroy()  # 关闭新窗口
        root.deiconify()  # 显示原窗口

    button1 = tk.Button(new_window, text="含有事务应用的删除操作：删除图书及其交易记录", command=open_window1)
    button1.grid(row=0, column=0, padx=10, pady=10)

    button2 = tk.Button(new_window, text="触发器控制下的添加操作：增加图书并触发作者记录更改", command=open_window2)
    button2.grid(row=1, column=0, padx=10, pady=10)

    button3 = tk.Button(new_window, text="存储过程控制下的更新操作：更新图书并判断是否合法", command=open_window3)
    button3.grid(row=2, column=0, padx=10, pady=10)

    button4 = tk.Button(new_window, text="含有视图的查询操作：查询图书与作者信息", command=open_window4)
    button4.grid(row=3, column=0, padx=10, pady=10)

    button5 = tk.Button(new_window, text="返回", command=go_back)
    button5.grid(row=7, column=7, columnspan=2, padx=10, pady=10)

    new_window.protocol("WM_DELETE_WINDOW", go_back)  # 当窗口关闭时，显示上一级窗口
    new_window.mainloop()


def update_table(textbox, cursor, table_name):
    textbox.delete("1.0", tk.END)
    # 查询表的内容
    cursor.execute(f"SELECT * FROM {table_name}")
    rows = cursor.fetchall()

    # 在文本框中显示表的内容
    textbox.insert(tk.END, f"Table: {table_name}\n")
    for row in rows:
        textbox.insert(tk.END, f"{row}\n")


root = tk.Tk()

host_label = tk.Label(root, text="IP地址")
host_label.pack()
host_entry = tk.Entry(root)
host_entry.pack()
host_entry.insert(0, "localhost")  # 设置默认值

user_label = tk.Label(root, text="用户名")
user_label.pack()
user_entry = tk.Entry(root)
user_entry.pack()
user_entry.insert(0, "root")  # 设置默认值

password_label = tk.Label(root, text="密码")
password_label.pack()
password_entry = tk.Entry(root, show="*")
password_entry.pack()
password_entry.insert(0, "wjj20040123")  # 设置默认值

db_label = tk.Label(root, text="数据库名")
db_label.pack()
db_entry = tk.Entry(root)
db_entry.pack()
db_entry.insert(0, "librarydb")  # 设置默认值

connect_button = tk.Button(root, text="连接", command=connect_to_database)
connect_button.pack()

result_text = tk.Text(root)
result_text.pack()
# 添加退出按钮
exit_button = tk.Button(root, text="退出", command=root.destroy)
exit_button.pack()
root.mainloop()
